package sdu.ltp.entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

/**
 * 
 * 数据库外观模式
 * 
 * @author ljh_2015
 *
 */
/**
 * @author ljh_2015
 *
 */
public class DataBaseFascade {

    private final static Logger logger = LogManager.getLogger(DataBaseFascade.class);
    private final static DataBasePool pool = new DataBasePool();
    private final static ConventionHandle handle = new ConventionDefaultHandle();
    private final static DataBaseFascade _instance = new DataBaseFascade();

    public static DataBaseFascade newInstance() {
	return _instance;
    }

    /**
     * 文本存储
     * 将输入文本存储到 _new_text数据库中
     * @param text
     */
    public void TextStorage(String text) {
	String sql = "insert into _new_text(text,ismapped) values (?,0)";
	Connection conn = pool.createConnection();
	if(conn!=null) {
	    PreparedStatement pstat = null;
	    try {
		pstat = conn.prepareStatement(sql);
		pstat.setString(1, text);
		boolean result = pstat.execute();
		if(result) {
		    logger.info("save successfull: "+text);
		}
	    } catch (SQLException e) {
		e.printStackTrace();
	    } finally {
		if(pstat!=null) {
		    try {
			pstat.close();
		    } catch (SQLException e) {
			e.printStackTrace();
		    }
		}
	    }
	    pool.closeConnection(conn);
	}
    }
    
    /**
     * 文本查询
     * 根据输入的text进行数据库搜索
     * @param text
     * @return
     */
    public List<Integer> queryStorage(String text) {
	List<Integer> results = null;
	String querySql = "select * from _new_text where text='"+text+"'";
	Connection conn = pool.createConnection();
	try {
	    PreparedStatement pstat = conn.prepareStatement(querySql);
	    ResultSet rs = pstat.executeQuery(querySql);
	    results = new ArrayList<>();
	    while(rs.next()) {
		int id = rs.getInt("id");
		results.add(id);
	    }
	    rs.close();
	    pstat.close();
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    if(conn!=null) {
		pool.closeConnection(conn);
	    }
	}
	return results;
    }
    
    /**
     * 将句式分析映射结构存入数据库中
     * @param mapper
     */
    public void insertMapper(StyleMapper mapper) {
	String mapper_insert_sql="insert into _new_mapper(sentid,mapper_key,mapper_pos,mapper_rel,mapper_word) values(?,?,?,?,?)";
	Connection conn = pool.createConnection();
	try {
	    PreparedStatement pstat = conn.prepareStatement(mapper_insert_sql);
	    pstat.setLong(1, mapper.getSentid());
	    pstat.setString(2, mapper.getSort_key());
	    pstat.setString(3, mapper.getPos_lates());
	    pstat.setString(4, mapper.getRel_lates());
	    pstat.setString(5, mapper.getWord_lates());
	    pstat.execute();
	    pstat.close();
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(conn);
	}
    }
    
    /**
     * 更新文本状态
     * @param id
     */
    public void update(int id) {
	String update_sql = "update _new_text set ismapped=1 where id="+id;
	Connection conn = pool.createConnection();
	try {
	    Statement st = conn.createStatement();
	    st.executeUpdate(update_sql);
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(conn);
	}
    }
    
    /**
     * 获取文本的状态
     * @param id
     * @return
     */
    public boolean isMapped(int id) {
	String is_mapped_sql = "select * from _new_text where ismapped=1 and id="+id;
	Connection conn = pool.createConnection();
	try {
	    Statement st = conn.createStatement();
	    ResultSet rs = st.executeQuery(is_mapped_sql);
	    if(rs.next()) {
		return true;
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(conn);
	}
	return false;
    }
    
    public void staticsRels() {
	String list_mapped_sql = "select * from _new_mapper where is_statics=0 for update";
	Connection conn = pool.createConnection();
	try {
	    Statement st = conn.createStatement();
	    ResultSet rs = st.executeQuery(list_mapped_sql);
	    while(rs.next()) {
		String relate = rs.getString("mapper_rel");
		int id = rs.getInt("id");
		String code = ECode.getDefaultCodeId(relate);
		update_statics_status(id);
		insertMappedRels(id,code);
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(conn);
	}
    }
    
    public void transferRels() {
	String trans_mapped_sql="select * from _new_mapper for update";
	Connection conn = pool.createConnection();
	try {
	    Statement st = conn.createStatement();
	    ResultSet rs = st.executeQuery(trans_mapped_sql);
	    while(rs.next()) {
		String relate = rs.getString("mapper_rel");
		int id = rs.getInt("id");
		String rel = ECode.getDefaultCodeId(relate);
		insertMappedRels(id,rel);
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(conn);
	}
    }
    
    /**
     * 添加map映射
     * @param id
     * @param rel
     */
    private void insertMappedRels(int id,String rel) {
	String insert_map_rel="insert into _new_mapper_rel values("+id+",'"+rel+"')";
	Connection conn = pool.createConnection();
	try{
	    Statement st = conn.createStatement();
	    st.execute(insert_map_rel);
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(conn);
	}
    }
    
    /**
     * 更新统计状态
     * @param id
     */
    private void update_statics_status(int id) {
	String update_mapped_status_sql = "update _new_mapper set is_statics=1 where id="+id;
	Connection conn = pool.createConnection();
	try{
	    Statement st = conn.createStatement();
	    st.executeUpdate(update_mapped_status_sql);
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(conn);
	}
    }
    
    /**
     * 规约化数据库更新
     * @param sentid
     * @param key
     * @param values
     */
    public void setConventionUpdate(long sentid,String key,String values) {
	String update_convention="update _new_mapper set rel_keys='"+key+"',rel_indexs='"+values+"' where id="+sentid;
	Connection conn = pool.createConnection();
	try{
	    Statement st = conn.createStatement();
	    st.executeUpdate(update_convention);
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(conn);
	}
    }
    
    /**
     * 对样例数据库进行规约
     * 一次性执行过程
     */
    public void convention() {
	String select_sql = "select id,mapper_rel from _new_mapper where rel_keys = null for update";
	Connection conn = pool.createConnection();
	try {
	    Statement st = conn.createStatement();
	    ResultSet rs = st.executeQuery(select_sql);
	    while(rs.next()) {
		String relate = rs.getString("mapper_rel");
		int id = rs.getInt("id");
		Convention.conv_grouping(Convention.decladding(relate), id, handle);
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(conn);
	}
    }
    
    /**
     * 获取指定章节卷
     * @param bookid
     * @param articletitle
     * @return
     */
    public int findsort(int bookid,String articletitle) {
	String find_sql = "select count(*) as count from _new_article as info,_new_article_title as book where book.id="+bookid+" and chaptertitle='"+articletitle+"' and info.articletitle = book.article_title; ";
	Connection conn = pool.createConnection();
	try {
	    Statement st = conn.createStatement();
	    ResultSet rs = st.executeQuery(find_sql);
	    if(rs.next()) {
		int count = rs.getInt("count");
		return count;
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(conn);
	}
	return 0;
    }
    
    /**
     * 根据book_title获取其对应的id
     * @param title
     * @return
     */
    public int findBookid(String title) {
	String find_book_id = "SELECT id from _new_article_title WHERE article_title = '"+title+"'";
	Connection conn = pool.createConnection();
	int id = 0;
	try {
	    Statement st = conn.createStatement();
	    ResultSet rs = st.executeQuery(find_book_id);
	    if(rs.next()) {
		id = rs.getInt("id");
	    }
	    rs.close();
	    st.close();
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(conn);
	}
	return id;
    }
    
    
    /**
     * 根据id 获取该book所有的章节列表
     * @param bookid
     * @return
     */
    public List<String> findTitles(int bookid) {
	String find_title_sql="SELECT chaptertitle from _new_article as book, _new_article_title as title where book.articletitle = "
		+ "title.article_title and title.id="+bookid+" group by book.chaptertitle";
	List<String> titles = new ArrayList<>();
	Connection conn = pool.createConnection();
	try {
	    Statement st = conn.createStatement();
	    ResultSet rs = st.executeQuery(find_title_sql);
	    while(rs.next()) {
		String chaptertitle = rs.getString("chaptertitle");
		titles.add(chaptertitle);
	    }
	    rs.close();
	    st.close();
	    return titles;
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(conn);
	}
	return null;
    }
    
    /**
     * 获取语料库规约后的聚类key型
     * 并以数量的大小倒叙排序返回
     * 返回类型为key-value键值对
     * <li>key为聚类key 例如：<SBV,VOB,WP></li>
     * <li>value为聚类value 例如：<1,1,2></li>
     * @return
     */
    public Map<String,Integer> getAllkeys() {
	String key_list = "SELECT rel_keys,count(*) as count FROM `_new_mapper` where mapper_key like '%_1_0' GROUP BY rel_keys ORDER BY count(*) DESC";
	Map<String,Integer> keys = new LinkedHashMap<>();
	Connection conn = pool.createConnection();
	try {
	    Statement st = conn.createStatement();
	    ResultSet rs = st.executeQuery(key_list);
	    while(rs.next()) {
		String key = rs.getString("rel_keys");
		Integer value = rs.getInt("count");
		keys.put(key, value);
	    }
	    rs.close();
	    st.close();
	    return keys;
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(conn);
	}
	return null;
    }
}
